#!/usr/bin/env python
# -*- encoding: utf-8 -*-

import pandas as pd

from xpy3lib.XRetryableQuery import XRetryableQuery
from xpy3lib.XRetryableSave import XRetryableSave
from sicost.AbstractDPJob import AbstractDPJob
from sicost.dp_common_job import DP011MJob__df7


class DP011_Z_A_Job(AbstractDPJob):
    """
    """

    cost_center_org = None
    data_type = None

    # NOTE p_wce_org =v_wce,
    # NOTE p_wce=LEFT(v_wce,1)||'@@@@'
    wce_org = None
    wce = None

    cal_type = None
    coef = None
    mon = None

    def __init__(self,
                 p_config=None,
                 p_db_conn_mpp=None,
                 p_db_conn_rds=None,
                 p_db_conn_dbprod7=None,
                 p_unit=None,
                 p_account=None,
                 p_cost_center_org=None,
                 p_cost_center=None,
                 p_account_period_start=None,
                 p_account_period_end=None,
                 p_data_type=None,
                 p_wce_org=None,
                 p_wce=None,
                 p_cal_type=None,
                 p_coef=None,
                 p_mon=None):
        """

        :param p_config:
        :param p_db_conn_mpp:
        :param p_db_conn_rds:
        :param p_db_conn_dbprod7:
        :param p_unit:
        :param p_account:
        :param p_cost_center_org:
        :param p_cost_center:
        :param p_account_period_start:
        :param p_account_period_end:
        :param p_data_type: data type是 0 D M。分别是 实时、每日、每月
        :param p_wce_org: 成本科目编号
        :param p_wce:
        :param p_cal_type:
        :param p_coef:
        :param p_mon:
        """
        super(DP011_Z_A_Job, self).__init__(p_config=p_config,
                                            p_db_conn_mpp=p_db_conn_mpp,
                                            p_db_conn_rds=p_db_conn_rds,
                                            p_db_conn_dbprod7=p_db_conn_dbprod7,
                                            p_unit=p_unit,
                                            p_account=p_account,
                                            p_cost_center=p_cost_center,
                                            p_account_period_start=p_account_period_start,
                                            p_account_period_end=p_account_period_end)
        self.cost_center_org = p_cost_center_org
        self.data_type = p_data_type
        self.wce_org = p_wce_org
        self.wce = p_wce
        self.cal_type = p_cal_type
        self.coef = p_coef
        self.mon = p_mon

    def do_execute(self):
        """
        """
        self.logger.info('DP011_Z_A_Job.do_execute')
        df, success = self.__step_0()
        if success is False:
            return
        df.columns = df.columns.str.upper()

        SOURCE_TABLE = df.loc[0]['SOURCE_TABLE']
        CAL_COLUMN = df.loc[0]['CAL_COLUMN']
        CAL_WHERE1 = df.loc[0]['CAL_WHERE1']
        CAL_OTHERS = df.loc[0]['CAL_OTHERS']
        CAL_WHERE2 = df.loc[0]['CAL_WHERE2']
        coef = str(self.coef)
        CAL_COLUMN = CAL_COLUMN.replace('P_COEF', coef)
        df1 = self.__step_2()
        if CAL_WHERE1 == '1=2':


            sql = " SELECT A.REC_ID AS REC_ID," \
                  " A.ACCT as ACCOUNT," \
                  " A.DEPARTMENT_CODE as FACTORY," \
                  " A.UNIT_CODE AS UNIT," \
                  " A.CLASS as TEAM," \
                  " A.SHIFT," \
                  " A.COST_CENTER," \
                  " A.PRODUCE_TIME AS WORK_TIME," \
                  " A.PRODUCE_START_TIME as PROCESS_START_TIME," \
                  " A.PRODUCE_END_TIME as  PROCESS_END_TIME," \
                  " A.BYPRODUCT_CODE as PRODUCT_CODE," \
                  " A.STEELNO as ST_NO," \
                  " A.INPUT_BYPRODUCT_CODE as IN_PRODUCT_CODE," \
                  " A.PROD_COILNO as MAT_NO," \
                  " A.ENTRY_COILNO as IN_MAT_NO," \
                  " A.APPTHROWAIMODE as APP_THROW_AI_MODE," \
                  " A.ANNEAL_CURVE as DESIGN_ANNEAL_DIAGRAM_CODE," \
                  " A.TRIM_FLAG," \
                  " A.MAT_ACT_WIDTH," \
                  " A.MAT_ACT_THICK," \
                  " A.ENTRY_MAT_WIDTH as IN_MAT_WIDTH," \
                  " A.ENTRY_MAT_THICK as IN_MAT_THICK," \
                  " A.PLAN_NO," \
                  " A.TRIMMING_AMT as TRIM_WIDTH," \
                  " A.ENTRY_MAT_INDIA as IN_MAT_INNER_DIA," \
                  " A.CUST_ORDER_NO," \
                  " A.PICKLING_TRIMMING_FLAG as PICKL_TRIM_FLAG," \
                  " A.SORT_GRADE_CODE," \
                  " A.COATING_TYPE as LAYER_TYPE," \
                  " A.MAT_ACT_LEN," \
                  " A.MAT_ACT_AREA," \
                  " A.TOP_COATING_WT AS TOP_COAT_WT," \
                  " A.TOP_COATING_WT AS BOT_COAT_WT," \
                  " COALESCE (A.OUTPUT_WT, 0) AS WT," \
                  " A.ACT_OUTPUT_WT as ACT_WT," \
                  " A.INPUT_WT as IN_WT," \
                  " A.ACT_INPUT_WT as ACT_IN_WT," \
                  " A.PRODUCE_NICK_FLAG as LAS_NOTCH_FLAG," \
                  " %s," \
                  " 'BGRAGGCB' AS REC_REVISOR," \
                  " TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS') AS REC_REVISOR_TIME" \
                  " FROM " \
                  " BGTARAS1.T_ADS_FACT_SICB_DP0102 A" \
                  " WHERE 1=1 " \
                  " AND A.ACCT = '%s' " \
                  " AND A.COST_CENTER = '%s' " \
                  " AND A.UNIT_CODE='%s' " \
                  " AND A.DATA_TYPE = '%s'" \
                  " AND LEFT(PRODUCE_TIME,14) >='%s'" \
                  " AND LEFT(PRODUCE_TIME,14) <'%s'" % (CAL_COLUMN,
                                                        self.account,
                                                        self.cost_center_org,
                                                        self.unit,
                                                        self.data_type,
                                                        self.account_period_start,
                                                        self.account_period_end)
            # P_ACCOUNT ，P_COST_CENTER_ORG，P_UNIT，P_DATA_TYPE，P_ACCOUNT_PERIOD_START，P_ACCOUNT_PERIOD_END
            self.logger.info(sql)
            df3 = XRetryableQuery(p_db_conn=self.db_conn_rds, p_sql=sql, p_max_times=5).redo()
            df3.columns = df3.columns.str.upper()



        else:
            df1 = self.__step_2()
            df2, success = self.__step_3(CAL_WHERE2, CAL_COLUMN, SOURCE_TABLE, CAL_OTHERS)
            if success is False:
                return
            df2.columns = df2.columns.str.upper()
            v = eval(CAL_WHERE1)

            df3 = pd.merge(df1, df2, on=v, how='left')

        self.logger.info(df3)
        #df31 = df3[df3['WCE'] == '']
        df31 = df3[df3['WCE'] == self.wce_org]

        df31.drop(['REC_ID'], axis=1, inplace=True)
        df31['REC_CREATOR'] = df31['REC_REVISOR']
        df31['REC_CREATE_TIME'] = df31['REC_REVISOR_TIME']

        df31.rename(columns={'ACCOUNT': 'ACCT'}, inplace=True)
        df31.rename(columns={'FACTORY': 'DEPARTMENT_CODE'}, inplace=True)
        df31.rename(columns={'UNIT': 'UNIT_CODE'}, inplace=True)
        df31.rename(columns={'TEAM': 'CLASS'}, inplace=True)
        df31.rename(columns={'WORK_TIME': 'PRODUCE_TIME'}, inplace=True)
        df31.rename(columns={'PROCESS_START_TIME': 'PRODUCE_START_TIME'}, inplace=True)
        df31.rename(columns={'PROCESS_END_TIME': 'PRODUCE_END_TIME'}, inplace=True)
        df31.rename(columns={'PRODUCT_CODE': 'BYPRODUCT_CODE'}, inplace=True)
        df31.rename(columns={'ST_NO': 'STEELNO'}, inplace=True)
        df31.rename(columns={'MAT_NO': 'PROD_COILNO'}, inplace=True)
        df31.rename(columns={'IN_PRODUCT_CODE': 'INPUT_BYPRODUCT_CODE'}, inplace=True)
        df31.rename(columns={'IN_MAT_NO': 'ENTRY_COILNO'}, inplace=True)
        df31.rename(columns={'WT': 'OUTPUT_WT'}, inplace=True)
        df31.rename(columns={'ACT_WT': 'ACT_OUTPUT_WT'}, inplace=True)
        df31.rename(columns={'IN_WT': 'INPUT_WT'}, inplace=True)
        df31.rename(columns={'ACT_IN_WT': 'ACT_INPUT_WT'}, inplace=True)
        df31.rename(columns={'WCE': 'COST_SUBJECT'}, inplace=True)
        df31.rename(columns={'ACT_N': 'COST_SUBJECT_ON_AMT'}, inplace=True)
        # df31.rename(columns={'CONSUME': 'UNITCONSUME'}, inplace=True)
        df31.rename(columns={'CONSUME_ITEM': 'CONSUME_PROJ'}, inplace=True)
        df31.rename(columns={'CONSUME_DESC': 'CONSUME_PROJ_DESC'}, inplace=True)
        df31.rename(columns={'CONSUME_UNIT': 'CONSUME_PROJ_UNIT'}, inplace=True)
        df31.rename(columns={'CONSUME_N': 'CONSUME_AMT'}, inplace=True)
        df31.rename(columns={'APP_THROW_AI_MODE': 'APPTHROWAIMODE'}, inplace=True)
        df31.rename(columns={'DESIGN_ANNEAL_DIAGRAM_CODE': 'ANNEAL_CURVE'}, inplace=True)
        df31.rename(columns={'IN_MAT_WIDTH': 'ENTRY_MAT_WIDTH'}, inplace=True)
        df31.rename(columns={'IN_MAT_THICK': 'ENTRY_MAT_THICK'}, inplace=True)
        df31.rename(columns={'TRIM_WIDTH': 'TRIMM_WIDTH'}, inplace=True)
        df31.rename(columns={'IN_MAT_INNER_DIA': 'ENTRY_MAT_INDIA'}, inplace=True)
        df31.rename(columns={'PICKL_TRIM_FLAG': 'PICKLING_TRIMMING_FLAG'}, inplace=True)
        df31.rename(columns={'LAYER_TYPE': 'COATING_TYPE'}, inplace=True)
        df31.rename(columns={'TOP_COAT_WT': 'TOP_COATING_WT'}, inplace=True)
        df31.rename(columns={'BOT_COAT_WT': 'BOT_COATING_WT'}, inplace=True)
        df31.rename(columns={'LAS_NOTCH_FLAG': 'PRODUCE_NICK_FLAG'}, inplace=True)


        XRetryableSave(p_db_conn=self.db_conn_rds, p_table_name='T_ADS_FACT_SICB_DP0103', p_schema='BGTARAS1',
                       p_dataframe=df31,
                       p_max_times=5).redo()

        super(DP011_Z_A_Job, self).do_execute()

    def __step_0(self, p_cal_type=None, p_coef=None, p_mon=None):
        # 子步骤0
        # 读取维护配置表
        # 读出关联的表名，关联字段，补充的SQL语句A表，补充的SQL语句B表，补充的字段A与B 等等 待补充 还没建 FIXME TBD????
        #
        # 1001 MIDF Q161 0 59068
        # 是传入的参数
        # p_account p_cost_center p_unit p_data_type P_WCE_ORG
        # 得到
        # 关联表的表名
        # merge需要on的字段
        # 拼接的SQL
        sql = " SELECT " \
              " RELATED_TBL AS SOURCE_TABLE, " \
              " RELATED_TERM_1 AS CAL_WHERE1," \
              " RELATED_TERM_2 AS CAL_WHERE2," \
              " COL_PROC AS CAL_COLUMN," \
              " OTHER_PROC AS CAL_OTHERS" \
              " FROM BGTARAS1.T_ADS_WH_SICB_DP011A" \
              " WHERE ACCT='%s'" \
              " AND COST_CENTER='%s'" \
              " AND UNIT_CODE='%s'" \
              " AND SOURCE_CODE='%s'" \
              " AND COST_SUBJECT ='%s'" % (self.account,
                                  self.cost_center,
                                  self.unit,
                                  self.data_type,
                                  self.wce_org)
        self.logger.info(sql)
        df = XRetryableQuery(p_db_conn=self.db_conn_rds, p_sql=sql, p_max_times=5).redo()
        self.logger.info(df)
        success = df.empty is False
        #SOURCE_TABLE = df.loc[0]['SOURCE_TABLE'] if success else None
        #CAL_COLUMN = df.loc[0]['CAL_COLUMN'] if success else None
        #CAL_WHERE1 = df.loc[0]['CAL_WHERE1'] if success else None
        #CAL_OTHERS = df.loc[0]['CAL_OTHERS'] if success else None
        #CAL_WHERE2 = df.loc[0]['CAL_WHERE2'] if success else None

        return df, success

    def __step_1(self, p_cal_type=None, p_coef=None, p_mon=None):
        # 子步骤1
        # IF P_WCE ==14803
        # 其中两个字段从B表中读取
        # COAT_T_COLUMN_A = ''
        # COAT_B_COLUMN_A = ''
        # COAT_T_COLUMN_B = 'B.COATE_WEIGHT_T_TOTAL_AVE AS TOP_COAT_WT,'
        # COAT_B_COLUMN_B = 'B.COATE_WEIGHT_B_TOTAL_AVE AS BOT_COAT_WT,'
        #
        # ELSE
        # 其中两个字段从A表中读取
        # COAT_T_COLUMN_A = 'A.TOP_COAT_WT AS TOP_COAT_WT,'
        # COAT_B_COLUMN_A = 'A.TOP_COAT_WT AS BOT_COAT_WT,'
        # COAT_T_COLUMN_B = ''
        # COAT_B_COLUMN_B = ''

        COAT_T_COLUMN_A = '' if self.wce_org == '14803' else 'A.TOP_COAT_WT AS TOP_COAT_WT,'
        COAT_B_COLUMN_A = '' if self.wce_org == '14803' else 'A.TOP_COAT_WT AS BOT_COAT_WT,'
        COAT_T_COLUMN_B = 'B.COATE_WEIGHT_T_TOTAL_AVE AS TOP_COAT_WT,' if self.wce_org == '14803' else ''
        COAT_B_COLUMN_B = 'B.COATE_WEIGHT_B_TOTAL_AVE AS BOT_COAT_WT,' if self.wce_org == '14803' else ''


    def __step_2(self):
        COAT_T_COLUMN_A = '' if self.wce_org == '14803' else 'A.TOP_COATING_WT AS TOP_COAT_WT,'
        COAT_B_COLUMN_A = '' if self.wce_org == '14803' else 'A.TOP_COATING_WT AS BOT_COAT_WT,'
        # 其中有汉字的地方根据情况补充进去
        # 1001，MIDF，Q161,0，20210225220000，20210226220000
        # P_ACCOUNT ，P_COST_CENTER_ORG，P_UNIT，P_DATA_TYPE，P_ACCOUNT_PERIOD_START，P_ACCOUNT_PERIOD_END
        sql = " SELECT A.REC_ID AS REC_ID," \
              " A.ACCT as ACCOUNT," \
              " A.DEPARTMENT_CODE as FACTORY," \
              " A.UNIT_CODE AS UNIT," \
              " A.CLASS as TEAM," \
              " A.SHIFT," \
              " A.COST_CENTER," \
              " A.PRODUCE_TIME AS WORK_TIME," \
              " A.PRODUCE_START_TIME as PROCESS_START_TIME," \
              " A.PRODUCE_END_TIME as  PROCESS_END_TIME," \
              " A.BYPRODUCT_CODE as PRODUCT_CODE," \
              " A.STEELNO as ST_NO," \
              " A.INPUT_BYPRODUCT_CODE as IN_PRODUCT_CODE," \
              " A.PROD_COILNO as MAT_NO," \
              " A.ENTRY_COILNO as IN_MAT_NO," \
              " A.APPTHROWAIMODE as APP_THROW_AI_MODE," \
              " A.ANNEAL_CURVE as DESIGN_ANNEAL_DIAGRAM_CODE," \
              " A.TRIM_FLAG," \
              " A.MAT_ACT_WIDTH," \
              " A.MAT_ACT_THICK," \
              " A.ENTRY_MAT_WIDTH as IN_MAT_WIDTH," \
              " A.ENTRY_MAT_THICK as IN_MAT_THICK," \
              " A.PLAN_NO," \
              " A.TRIMMING_AMT as TRIM_WIDTH," \
              " A.ENTRY_MAT_INDIA as IN_MAT_INNER_DIA," \
              " A.CUST_ORDER_NO," \
              " A.PICKLING_TRIMMING_FLAG as PICKL_TRIM_FLAG," \
              " A.SORT_GRADE_CODE," \
              " A.COATING_TYPE as LAYER_TYPE," \
              " A.MAT_ACT_LEN," \
              " A.MAT_ACT_AREA," \
              " %s" \
              " %s" \
              " COALESCE (A.OUTPUT_WT, 0) AS WT," \
              " A.ACT_OUTPUT_WT as ACT_WT," \
              " A.INPUT_WT as IN_WT," \
              " A.ACT_INPUT_WT as ACT_IN_WT," \
              " A.PRODUCE_NICK_FLAG as LAS_NOTCH_FLAG," \
              " 'BGRAGGCB' AS REC_REVISOR," \
              " TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS') AS REC_REVISOR_TIME" \
              " FROM " \
              " BGTARAS1.T_ADS_FACT_SICB_DP0102 A" \
              " WHERE 1=1 " \
              " AND A.ACCT = '%s' " \
              " AND A.COST_CENTER = '%s' " \
              " AND A.UNIT_CODE='%s' " \
              " AND A.DATA_TYPE = '%s'" \
              " AND LEFT(PRODUCE_TIME,14) >='%s'" \
              " AND LEFT(PRODUCE_TIME,14) <'%s'" % (COAT_T_COLUMN_A,
                                                             COAT_B_COLUMN_A,
                                                             self.account,
                                                             self.cost_center_org,
                                                             self.unit,
                                                             self.data_type,
                                                             self.account_period_start,
                                                             self.account_period_end)
        # P_ACCOUNT ，P_COST_CENTER_ORG，P_UNIT，P_DATA_TYPE，P_ACCOUNT_PERIOD_START，P_ACCOUNT_PERIOD_END
        self.logger.info(sql)
        df1 = XRetryableQuery(p_db_conn=self.db_conn_rds, p_sql=sql, p_max_times=5).redo()
        df1.columns = df1.columns.str.upper()
        self.logger.info(df1)
        success = df1.empty is False
        return  df1

    def __step_3(self, CAL_WHERE2, CAL_COLUMN, SOURCE_TABLE, CAL_OTHERS):
        # select
        # 'B表的字段'
        # 'B表的merge字段'
        # 'AB结合字段用到的B表字段'
        # from
        # B表的表名
        # where
        # B表的条件
        #
        # 得到dfB
        COAT_T_COLUMN_B = 'B.COATE_WEIGHT_T_TOTAL_AVE AS TOP_COAT_WT,' if self.wce_org == '14803' else ''
        COAT_B_COLUMN_B = 'B.COATE_WEIGHT_B_TOTAL_AVE AS BOT_COAT_WT,' if self.wce_org == '14803' else ''
        coef = str(self.coef)
        CAL_COLUMN = CAL_COLUMN.replace('P_COEF', coef)
        # 其中有汉字的地方根据情况补充进去
        # 1001，MIDF，Q161,0，20210225220000，20210226220000
        # P_ACCOUNT ，P_COST_CENTER_ORG，P_UNIT，P_DATA_TYPE，P_ACCOUNT_PERIOD_START，P_ACCOUNT_PERIOD_END
        sql = " SELECT " \
              " %s" \
              " %s" \
              " %s," \
              " %s" \
              " FROM " \
              " %s" \
              " WHERE 1=1 " \
              " %s" % (COAT_T_COLUMN_B, COAT_B_COLUMN_B, CAL_WHERE2, CAL_COLUMN, SOURCE_TABLE, CAL_OTHERS)

        self.logger.info(sql)
        df2 = XRetryableQuery(p_db_conn=self.db_conn_dbprod7, p_sql=sql, p_max_times=5).redo()

        self.logger.info(df2)
        success = df2.empty is False
        return  df2, success

    def __step_4(self):
        """
        dfA merge dfB left
        on 关联字段

        得到df
        :return:
        """
        pass


    def __step_5(self):
        """
        读取AB两表字段都有关的字段
        根据逻辑得到

        drop掉
        多余字段
        :return:
        """
        pass

    def __step_6(self):
        pass

    def __step_7(self):
        pass